﻿Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Public Class oHierarchy

    Public Function GetAllHierarchy(ByVal dataBaseName As String, ByVal companyCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oHierarchy")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.HierarchyID,T0.CreatedUserID,T0.CompanyCode, T1.CompanyName,T0.HierarchyName,T0.HierarchyType,T0.HierarchyCode,T2.*")
                query.Append(" FROM oHierarchy T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.HierarchyType = T2.ReferenceValue AND  T2.ReferenceCode ='HierarchyType')")
                query.Append(" WHERE T0.CompanyCode  = @Param1 ")
                query.Append(" ORDER BY  T0.HierarchyID DESC")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetLastRecordHierarchy(ByVal dataBaseName As String, ByVal companyCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oHierarchy")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT TOP 1 * FROM oHierarchy T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.HierarchyType = T2.ReferenceValue AND  T2.ReferenceCode ='HierarchyType')")
                query.Append(" WHERE T0.CompanyCode  = @Param1 ")
                query.Append(" ORDER BY HierarchyID DESC ")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetHierarchyByID(ByVal dataBaseName As String, ByVal hierarchyID As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oHierarchy")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT * FROM oHierarchy T0 LEFT JOIN oReferenceValue T1 ON (T0.HierarchyType = T1.ReferenceValue AND  T1.ReferenceCode ='HierarchyType')")
                query.Append(" WHERE HierarchyID = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {hierarchyID})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function CreateHierarchy(ByVal dataBaseName As String, ByVal headerJson As String, ByVal hierarchyFatherID As String) As String
        Dim connect As New Connection()
        Try
            Dim jss As New JavaScriptSerializer()
            Dim header As oHierarchy = jss.Deserialize(Of oHierarchy)(headerJson)
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 AndAlso header IsNot Nothing Then
                Dim query As New StringBuilder
                query.Append("INSERT INTO [oHierarchy] ")
                query.Append(" ([CompanyCode]")
                query.Append(",[HierarchyCode]")
                query.Append(",[HierarchyName]")
                query.Append(",[HierarchyType]")
                query.Append(",[CreatedUserID])")
                query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5)")

                Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {header.CompanyCode, header.HierarchyCode, header.HierarchyName, _
                                                                                       header.HierarchyType, header.CreatedUserID})
                If (count > 0) Then
                    'Get Max Id
                    Dim ds As DataSet = connect.ObjectGetAll_Query_SAP("SELECT IDENT_CURRENT('oHierarchy')")
                    'Insert oHiererchyDetail
                    If ds IsNot Nothing And ds.Tables.Count > 0 Then
                        Dim objHierarchyDetail As New oHierarchyDetail
                        Dim newHierarchyID As Integer = Integer.Parse(ds.Tables(0).Rows(0)(0).ToString())
                        errMsg = objHierarchyDetail.CreateHierarchyDetail(dataBaseName, newHierarchyID.ToString(), "", hierarchyFatherID)
                        If errMsg.Length > 0 Then
                            DeleteHierarchy(dataBaseName, newHierarchyID)
                        Else
                        End If
                    End If
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function UpdateJsonHiererchy(ByVal hierarchyID As String, _
                                       ByVal hierarchyCode As String, ByVal hierarchyName As String, ByVal companyCode As String, _
                                       ByVal createdUserId As String, ByVal hierarchyType As String, ByVal databaseName As String, ByVal isInsert As Boolean) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(databaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {hierarchyCode, hierarchyName, companyCode, hierarchyType, createdUserId}
                Dim query As New StringBuilder
                If isInsert = True Then
                    query.Append(" INSERT INTO [oHierarchy]")
                    query.Append("([HierarchyCode]")
                    query.Append(",[HierarchyName]")
                    query.Append(",[CompanyCode]")
                    query.Append(",[HierarchyType]")
                    query.Append(",[CreatedUserID]) ")
                    query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5)")
                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
                Else
                    query.Append("UPDATE  [oHierarchy] SET ")
                    query.Append("  [HierarchyCode] = @Param1")
                    query.Append("  ,[HierarchyName] =  @Param2")
                    query.Append("  ,[HierarchyType] =  @Param3")
                    query.Append(" WHERE [HierarchyID] = @Param4")
                    connect.Object_Execute_SAP(query.ToString(), New Object() {hierarchyCode, hierarchyName, hierarchyType, hierarchyID})
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception

            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function DeleteHierarchy(ByVal dataBaseName As String, ByVal hierarchyID As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {hierarchyID}
                Dim query As New StringBuilder
                query.Append(" DELETE FROM [oHierarchy]")
                query.Append("  WHERE oHierarchy.HierarchyID =  @Param1")
                query.Append(" DELETE FROM [oHierarchyDetail]")
                query.Append("  WHERE oHierarchyDetail.HierarchyID =  @Param1")
                Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Class oHierarchy
        Property HierarchyID As Integer
        Property CompanyCode As String
        Property HierarchyCode As String
        Property HierarchyName As String
        Property HierarchyType As String
        Property CreatedUserID As String
        Property CreatedDate As DateTime
        Public Sub New()
        End Sub
    End Class
End Class

